//
//  LTFTopicBaseHandel.m
//  NeteaseNews
//
//  Created by liutaifeng on 16/4/30.
//  Copyright © 2016年 liutaifeng. All rights reserved.
//

#import "AccountBookSQL.h"
#import "FMDB.h"


@interface AccountBookSQL ()

@end

@implementation AccountBookSQL
//单例
+ (instancetype)shareDataBaseTool {
    static AccountBookSQL *handel = nil;
    static dispatch_once_t token;
    dispatch_once(&token, ^{
        handel = [[AccountBookSQL alloc] init];
        [handel openDB];
        [handel createTable];
    });
    return handel;
}
//打开数据库
- (void)openDB {
    NSString *sandBox = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *docPath = [sandBox stringByAppendingPathComponent:@"accountBook.sqlite"];
    NSLog(@"%@", docPath);
    int result = sqlite3_open([docPath UTF8String], &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    }
    else {
        NSLog(@"打开失败");
    }
}
//创建表格
- (void)createTable {
    NSString *sqlStr = @"create table if not exists accountBook (accountBookName text primary key, accountBookCover text, accountBookBackground text)";
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建成功");
    }
    else {
        NSLog(@"创建失败: %d", result);
    }
}
//添加
- (BOOL)insertModel:(AccountBookModel *)model {
    NSString *sqlStr = [NSString stringWithFormat:@"insert into accountBook (accountBookName, accountBookCover, accountBookBackground) values('%@', '%@', '%@')", model.accountBookName, model.accountBookCover, model.accountBookBackground];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"添加成功");
        return YES;
        

    }
    else {
        NSLog(@"添加失败: %d", result);
        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"账本名重复, 请修改后添加" message:nil delegate:nil cancelButtonTitle:@"确认" otherButtonTitles:nil, nil];
        [alert show];
        return NO;
    }
}
//查询
- (NSMutableArray *)select {
    // *号是通配符, 表示查询某个条件下, 数据库中所有字段内容
    NSString *selectSql = @"select * from accountBook";
    //创建一个跟随指针, 这个指针用来循环便利数据库中的每行代码.
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, [selectSql UTF8String], -1, &stmt, nil);
    NSMutableArray *modelArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //如果跟随指针所指向的位置有一行, 则循环继续, 如果没有, 循环结束
            const unsigned char *accountBookName = sqlite3_column_text(stmt, 0);
            NSString *name = [NSString stringWithUTF8String:(const char*)accountBookName];
            const unsigned char *accountBookCover = sqlite3_column_text(stmt, 1);
            NSString *cover = [NSString stringWithUTF8String:(const char*)accountBookCover];
            const unsigned char *accountBookBackground = sqlite3_column_text(stmt, 2);
            NSString *background = [NSString stringWithUTF8String:(const char*)accountBookBackground];
            AccountBookModel *model = [[AccountBookModel alloc] init];
            model.accountBookName = name;
            model.accountBookCover = cover;
            model.accountBookBackground = background;
            
            [modelArray addObject:model];
        }
        
        return modelArray;
        
        
        
        
    }
    else {
        NSLog(@"查询错误: %d", result);
        return nil;
    }
    
}
//删除
- (void)deleteCollect:(AccountBookModel *)model {
    NSString *sqlStr = [NSString stringWithFormat:@"delete from accountBook where accountBookName = '%@'", model.accountBookName];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }
    else {
        NSLog(@"删除失败: %d", result);
    }
}

//更新
- (void)updateModel:(AccountBookModel *)model name:(NSString *)name {
    NSString *sqlStr = [NSString stringWithFormat:@"update accountBook set accountBookName = '%@', accountBookCover = '%@', accountBookBackground = '%@' where accountBookName = '%@'", model.accountBookName, model.accountBookCover, model.accountBookBackground, name];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    }
    else {
        NSLog(@"更新失败: %d", result);
    }
}



@end
